package com.qiaodata.datatrans.utils;

import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.*;

/**
 * Created by Administrator on 2016/11/16 0016.
 */
@Slf4j
public class PostgresqlUtil {

    public static Connection getPostgreConnection() throws SQLException {
        Connection connection = null;
        try {
//            String url = "jdbc:postgresql://127.0.0.1:5432/test";//换成自己PostgreSQL数据库实例所在的ip地址，并设置自己的端口
//            String user = "postgres";
//            String password = "root";  //在这里我的密码为空，读者可以自己选择是否设置密码
            String url = "jdbc:postgresql://192.168.6.120:5432/fjl_resume";//换成自己PostgreSQL数据库实例所在的ip地址，并设置自己的端口
            String user = "postgres";
            String password = "5432@pk_id";
            Class.forName("org.postgresql.Driver");  //一定要注意和上面的MySQL语法不同
            connection= (Connection) DriverManager.getConnection(url, user, password);
            return connection;
        } catch (Exception e) {
            log.error("连接错误！");
        }
        return connection;
    }

    /**
     * 通过电话获取一条信息的电话及最新的时间记录，如果返回null，则表示巧达没有此记录
     * @param tel_id
     * @return
     * @throws Exception
     */
    public static JSONObject getOneNewResumer(String tel_id, String email) throws Exception {
        //正则表达式判断入参是否都是数字
        String regex = "^[0-9]+$";
        boolean b = tel_id.matches(regex);
        if (!b) {
            //输入参数含有数字，防止sql注入
            return null;
        }
        String sql = "select refresh_time, contact->>'mobile' AS mobile_qd, contact->>'email' AS email_qd from fjl.resume where contact->>'mobile'::VARCHAR ='"+tel_id+"'"+" OR contact->>'email'::VARCHAR ='"+email+"'";
        JSONObject jso = new JSONObject();
        Connection connection = getPostgreConnection();
        Statement statement = connection.createStatement();
        try {
            ResultSet rs = statement.executeQuery(sql);
            List<Long> list = new ArrayList<>();
            HashMap<Long, Object> map = new HashMap<>();
            while (rs.next()) {
                //取出列值
                long refreshTime = rs.getLong(1);//数据库中字段的位置是23
                String mobile_qd = rs.getString(2);
                String email_qd = rs.getString(3);
                if (refreshTime==0) {
                    continue;
                }
                map.put(refreshTime, mobile_qd+","+email_qd);
                list.add(refreshTime);
            }
            if (list==null || list.size()<1) {
                return null;
            }
            if (list.size()==1) {
                jso.put("tel_id", tel_id);
                jso.put("email", email);
                jso.put("refresh_time", list.get(0));
                return jso;
            }
            //取出时间最大的那条记录
            setValueDesc(list, 0);
            Long refreshTimeMax = list.get(0);

            //获取时间最新的记录
            String mobile_email = (String) map.get(refreshTimeMax);
            String[] me = mobile_email.split(",");
            if (me.length!=2) {//如果电话和邮箱只有一个
                if (me[0]==null || "".equals(me[0])) {
                    boolean f = me[0].matches(regex);
                    if (f) {//全是数字，认为是手机号码
                        jso.put("tel_id", me[0]);
                        jso.put("email", "");
                        jso.put("refresh_time", refreshTimeMax);
                    }
                    //否则认为是邮箱
                    jso.put("tel_id", "");
                    jso.put("email", me[0]);
                    jso.put("refresh_time", refreshTimeMax);
                }
            }
            //二者都有
            jso.put("tel_id", me[0]);
            jso.put("email", me[1]);
            jso.put("refresh_time", refreshTimeMax);
            return jso;

        } catch (Exception e) {
            log.error("查询出错！", e);
        } finally {
            if (null!=statement) {
                statement.close();
                statement = null;
            }
            if (null!=connection) {
                connection.close();
                connection = null;
            }
        }
        return null;
    }

    /**
     * 冒泡排序
     *需要排序的集合
     * @param list
     * 排序类型 0：正序 1：倒序
     * @param type
     *
     * */
    public static void setValueDesc(List<Long> list, int type) {
        for (int i = 0; i < list.size(); i++) {// 循环List集合，开始于下标0
            for (int j = i + 1; j < list.size(); j++) {// 循环List集合，开始于下标0+1
                Long a = list.get(i);// 取List集合的第n个值
                Long b = list.get(j);// 取List集合的第n+1个值
                if (type == 0 ? a < b : a > b) {// 如果a比小，则替换两个值的位置。
                    list.set(i, b);
                    list.set(j, a);
                }
            }
        }
    }

    public static void main(String[] args) {
//        List<Long> list = new ArrayList<>();
//        list.add(4l);
//        list.add(1l);
//        list.add(3l);
//        list.add(8l);
//        list.add(6l);
//
//        setValueDesc(list, 0);
//        Long aLong = list.get(0);
//
//        System.out.println(aLong);
        try {
            JSONObject oneNewResumer = getOneNewResumer("15177798860a", "");
            System.out.println(oneNewResumer.toJSONString());
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

}
